package IOTWeb.com.utils;

import java.io.IOException;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import org.apache.commons.lang.StringUtils;
/*
 * mqttsql�ն˶�̬�ุ��
 * @author xu-zhiqiang
 * @create 2021-11-10 9:21
 
 */
public class ParseClassSql {
    private Connection conn;
    // ����sql���
    private Statement stt;
    // �����
    private ResultSet set;


    public void main(String[] args) throws SQLException, ClassNotFoundException{
//        //�����
//    	Map<String, String> map = new HashMap<>();
//    	map.put("ID", "1");
//    	map.put("temp", "17");
//    	colnum(map);
    }

    
    public Map<String, String> analysis(String a){
        Map<String, String> map = new HashMap<>();
        String[] split = StringUtils.splitPreserveAllTokens(a,",");
        for (String s:split){
            String[] splitb = StringUtils.splitPreserveAllTokens(s,"=");
            map.put(splitb[0],splitb[1]);
        }

        return map;
    }


    public Map<Integer, String> sqls(String name, Map<String, String> map){
        Map<Integer,String> mper = new HashMap<>();
        int i =1;
        Set<String> set = map.keySet();
        Iterator<String> it =set.iterator();
        while(it.hasNext()) {
            //�õ�ÿһ��key
            String key = it.next();
            //ͨ��key��ȡ��Ӧ��value
            String value = map.get(key);
            mper.put(i, "INSERT INTO " + name + " VALUES ('" + key + "','" + value + "');");
            i++;
        }
        return mper;
    }


    public void adds(Map<Integer, String> map) {
    	LogSet logSet = new LogSet();
    	JDUtil jDUtil = new JDUtil();
        try {
            Set<Integer> keySet = map.keySet();
            Iterator<Integer> it =keySet.iterator();
            while(it.hasNext()) {
                Integer key = it.next();
                //ͨ��key��ȡ��Ӧ��value
                String AddSQL = map.get(key);
                // ��ȡ����
                conn = jDUtil.getConnection("URLDev");
                if (conn == null)
                    return;
                // ����һ��Statement����
                PreparedStatement ps = conn.prepareStatement(AddSQL);
//            ps.setString(1, name);
//            ps.setString(2, jsonArray);

                // ִ��sql���
                ps.executeUpdate();
            }
        } catch (Exception e) {
        	logSet.logger.severe(e.toString());
            e.printStackTrace();
        } finally {
            try {
                conn.close();

            } catch (Exception e2) {
            	logSet.logger.severe(e2.toString());
            }
        }
    }
    
    public void colnum(Map<String, String> map) throws SQLException, ClassNotFoundException, IOException {
        //�����
        String t = new SimpleDateFormat("yyyy").format(new Date());
        sqlOption(map,t,"year");
        //�»���
        t = new SimpleDateFormat("yyyy/MM").format(new Date());
        sqlOption(map,t,"Month");
        //�����
        t = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
        sqlOption(map,t,"Day");
        //Сʱ����
        t = new SimpleDateFormat("yyyy/MM/dd-HH").format(new Date());
        sqlHOption(map,t,"Hour");
        //�������ֵ��
        sqlMaxOption(map,"Max");
        //������Сֵ��
        sqlMinOption(map,"Min");
        
    }

    public void sqlMinOption(Map<String, String> map,String type) throws SQLException, IOException {
    	JDUtil jDUtil = new JDUtil();
    	conn = jDUtil.getConnection("URLDev");
        stt = conn.createStatement();
        set = stt.executeQuery("SELECT count(*) rec FROM COLNUM WHERE type='"+type+"'");
        
    	int rowCount = 0;
        while(set.next()){
        	rowCount= set.getInt("rec");
        }
        if(rowCount==0) {
            String a = map.get("temp");
            int i = Integer.parseInt(a);
            int c = 0;
        	PreparedStatement ps = conn.prepareStatement("INSERT INTO COLNUM VALUES(' ','temp',"+i+","+c+",'"+type+"')");
        	ps.executeUpdate();
        }else {
        	set = stt.executeQuery("select * from COLNUM where type='"+type+"'");
            while (set.next()) {
                int s = set.getInt("value");
                String a = map.get("temp");
                int i = Integer.parseInt(a);
                if (i < s) {
	                PreparedStatement ps = conn.prepareStatement("UPDATE COLNUM SET VALUE="+i+" WHERE type='"+type+"'");
	                // ִ��sql���
	                ps.executeUpdate();
                }
            }
        }
    }
    
    public void sqlMaxOption(Map<String, String> map,String type) throws SQLException, IOException {
    	JDUtil jDUtil = new JDUtil();
    	conn = jDUtil.getConnection("URLDev");
        stt = conn.createStatement();
        set = stt.executeQuery("SELECT count(*) rec FROM COLNUM WHERE type='"+type+"'");
        
    	int rowCount = 0;
        while(set.next()){
        	rowCount= set.getInt("rec");
        }
        if(rowCount==0) {
            String a = map.get("temp");
            int i = Integer.parseInt(a);
            int c = 0;
        	PreparedStatement ps = conn.prepareStatement("INSERT INTO COLNUM VALUES(' ','temp',"+i+","+c+",'"+type+"')");
        	ps.executeUpdate();
        }else {
        	set = stt.executeQuery("select * from COLNUM where type='"+type+"'");
            while (set.next()) {
                int s = set.getInt("value");
                String a = map.get("temp");
                int i = Integer.parseInt(a);
                if (i > s) {
	                PreparedStatement ps = conn.prepareStatement("UPDATE COLNUM SET VALUE="+i+" WHERE type='"+type+"'");
	                // ִ��sql���
	                ps.executeUpdate();
                }
            }
        }
    }
     
    public void sqlOption(Map<String, String> map,String t,String type) throws SQLException, IOException {
    	JDUtil jDUtil = new JDUtil();
    	conn = jDUtil.getConnection("URLDev");
        stt = conn.createStatement();
        set = stt.executeQuery("SELECT count(*) rec FROM COLNUM WHERE TIME='"+t+"'");
        
    	int rowCount = 0;
        while(set.next()){
        	rowCount= set.getInt("rec");
        }
        if(rowCount==0) {
            String a = map.get("temp");
            int i = Integer.parseInt(a);
            int c = 1;
        	PreparedStatement ps = conn.prepareStatement("INSERT INTO COLNUM VALUES('"+t+"','temp',"+i+","+c+",'"+type+"')");
        	ps.executeUpdate();
        	System.out.println("����һ���µ�ֵ");
        }else {
        	set = stt.executeQuery("select * from COLNUM where time='"+t+"' and type='"+type+"'");
            while (set.next()) {
                int c = set.getInt("count");
                int s = set.getInt("value");
                String a = map.get("temp");
                int i = Integer.parseInt(a);
                System.out.println("i��"+i);
                c++;
                int v = (i+s)/c;
                System.out.println("value�ǣ�"+v+"count�ǣ�"+c);
                PreparedStatement ps = conn.prepareStatement("UPDATE COLNUM SET VALUE="+v+",COUNT="+c+" WHERE TIME ='"+t+"' and type='"+type+"'");
                // ִ��sql���
                ps.executeUpdate();
            }
        }
    }
    
    public void sqlHOption(Map<String, String> map,String t,String type) throws SQLException, IOException {
    	JDUtil jDUtil = new JDUtil();
    	conn = jDUtil.getConnection("URLDev");
        stt = conn.createStatement();
        set = stt.executeQuery("SELECT count(*) rec FROM COLNUM WHERE TIME='"+t+"'");
    	int rowCount = 0;
        while(set.next()){
        	rowCount= set.getInt("rec");
        }
        if(rowCount==0) {
            int i = 0;
            while(i<24) {
            	String string = new DecimalFormat("00").format(i);
    		    String tt = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
    		    String ti = tt+"-"+string;
		        PreparedStatement ps = conn.prepareStatement("INSERT INTO COLNUM VALUES('"+ti+"','temp',0,0,'"+type+"')");
		        ps.executeUpdate();
		        i++;
            }
        }else {
            set = stt.executeQuery("select * from COLNUM where time='"+t+"'");
            while(set.next()) {
                int c = set.getInt("count");
                int s = set.getInt("value");
                String a = (String)map.get("temp");
                int i = Integer.parseInt(a);
                ++c;
                int v = (i + s) / c;
                PreparedStatement ps = conn.prepareStatement("UPDATE COLNUM SET VALUE=" + v + ",COUNT=" + c + " WHERE TIME ='" +t+ "'");
                ps.executeUpdate();
            }
        }
    }
    
    public String toEqualString(Map<?, ?> map, char separator) {
        List<String> result = new ArrayList<>();
        map.entrySet().parallelStream().reduce(result, (first, second)->{
            first.add(second.getKey() + "=" + second.getValue());
            return first;
        }, (first, second)->{
            if (first == second) {
                return first;
            }
            first.addAll(second);
            return first;
        });

        return StringUtils.join(result, separator);
    }
   
}
